﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyThuVien.DTO;
using System.Data;
using QuanLyThuVien.Supporter;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng DOCGIA
    //----------------------------------------------------------------------
    public class DALDocGia
    {
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALDocGia()
        { }

        //----------------------------------------------------------------------
        //Desc: chuyển dòng dữ liệu thành DOCGIA
        //----------------------------------------------------------------------
        public DTODocGia ConvertToDocGia(DataRow row)
        {
            DTODocGia docgia = new DTODocGia();

            docgia.MaDG = row["MaDocGia"].ToString();
            docgia.HoTen = row["HoTen"].ToString();
            docgia.DiaChi = row["DiaChi"].ToString();
            docgia.Email = row["Email"].ToString();
            docgia.LoaiDG.MaLoaiDG = row["MaLoaiDG"].ToString();
            try
            {
                docgia.NgaySinh = DateTime.Parse(row["NgaySinh"].ToString());
            }
            catch
            { }
            try
            {
                docgia.NgayLapThe = DateTime.Parse(row["NgayLapThe"].ToString());
            }
            catch
            { }

            try
            {
                docgia.TongNo = Double.Parse(row["TongNo"].ToString());
            }
            catch
            { }

            try
            {
                docgia.NgayHetHan = DateTime.Parse(row["NgayHetHan"].ToString());
            }
            catch
            { }

            return docgia;
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách độc giả
        //----------------------------------------------------------------------
        public DataTable LayDSDocGia()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDocGia_LayDanhSachDG";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách độc giả có thẻ hết hạn
        //----------------------------------------------------------------------
        public DataTable LayDSDocGiaHetHan()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDocGia_LayDanhSachDGHetHan";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy thông ti độc giả
        //----------------------------------------------------------------------
        public DTODocGia LayThongTinDG(string madg)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDocGia_LayThongTinDG";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15)
            { Value = madg };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDocGia);

            if (data == null || data.Rows.Count == 0)
                return null;
            return ConvertToDocGia(data.Rows[0]);
        }

        //----------------------------------------------------------------------
        //Desc: lấy thông tin độc giả, trả về datatable
        //----------------------------------------------------------------------
        public DataTable LayThongTinDocGia(string madg)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDocGia_LayThongTinDG";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15) { Value = madg };
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDocGia);
        }

        //----------------------------------------------------------------------
        //Desc: đếm độc giả theo năm
        //----------------------------------------------------------------------
        public int DemDGTheoNam(long nam)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            int dem;
            string spName = "spDocGia_DemDGTheoNam";
            SqlParameter sqlprNam = new SqlParameter("@Nam", DbType.Int32) { Value = nam };

            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprNam);
            if (obj == null)
                return -1;
            else
            {
                try
                {
                    dem = (int)obj;
                }
                catch
                {
                    return -1;
                }

                return dem;
            }
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã độc giả
        //----------------------------------------------------------------------
        public int DemMaDG(string madg)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spDocGia_DemMaDG";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15)
            { Value = madg };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaDocGia);

            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch
            {
                return -1;
            }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm số phiếu mượn của độc giả
        //----------------------------------------------------------------------
        public int DemSoPhieuMuonCuaDG(string madocgia)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spDocGia_DemSoPhieuMuon";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15) { Value = madocgia };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaDocGia);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: lấy tổng nợ
        //----------------------------------------------------------------------
        public double LayTongNo(string madg)
        {
            if (DatabaseManager.DbConnection == null)
                return -1.0F;
            string spName = "spDocGia_LayTongNo";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15)
            { Value = madg };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
            if (data == null || data.Rows.Count == 0)
                return -1.0F;
            double tongno;
            try
            {
                tongno = Double.Parse(data.Rows[0]["TongNo"].ToString());
                
            }
            catch
            {
                return -1.0;
            }
            return tongno;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật tổng nợ
        //----------------------------------------------------------------------
        public bool CapNhatTongNo(string madg, double tongno)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spDocGia_CapNhatTongNo";
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15) { Value = madg };
            SqlParameter sqlprTongNo = new SqlParameter("@TongNo", SqlDbType.Money) { Value = tongno };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprMaDocGia, sqlprTongNo);
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật độc giả
        //----------------------------------------------------------------------
        public bool CapNhat(DTODocGia docgia)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spDocGia_CapNhatDG";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac" ,SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15)
            { Value =  docgia.MaDG};
            SqlParameter sqlprMaLoaiDG = new SqlParameter("@MaLoaiDG", SqlDbType.VarChar, 15) { Value =  docgia.LoaiDG.MaLoaiDG};
            SqlParameter sqlprHoTen = new SqlParameter("@HoTen", SqlDbType.NVarChar, 70) { Value =  docgia.HoTen};
            SqlParameter sqlprNgaySinh = new SqlParameter("@NgaySinh", SqlDbType.Date) { Value =  docgia.NgaySinh};
            SqlParameter sqlprDiaChi = new SqlParameter("@DiaChi", SqlDbType.NVarChar, 70) { Value = docgia.DiaChi };
            SqlParameter sqlprEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 70) { Value =  docgia.Email};
            SqlParameter sqlprNgayLapThe = new SqlParameter("@NgayLapThe", SqlDbType.Date) { Value = docgia.NgayLapThe };
            SqlParameter sqlprNgayHetHan = new SqlParameter("@NgayHetHan", SqlDbType.Date) { Value = docgia.NgayHetHan };
            SqlParameter sqlprTongNo = new SqlParameter("@TongNo", SqlDbType.Money) { Value =  docgia.TongNo};

            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaDocGia,
                sqlprMaLoaiDG, sqlprHoTen, sqlprNgaySinh, sqlprDiaChi, sqlprEmail, sqlprNgayLapThe, sqlprNgayHetHan, sqlprTongNo);
        }

        //----------------------------------------------------------------------
        //Desc: xóa
        //----------------------------------------------------------------------
        public bool Xoa(DTODocGia docgia)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spDocGia_CapNhatDG";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 0 };
            SqlParameter sqlprMaDocGia = new SqlParameter("@MaDocGia", SqlDbType.VarChar, 15) { Value = docgia.MaDG };
            SqlParameter sqlprMaLoaiDG = new SqlParameter("@MaLoaiDG", SqlDbType.VarChar, 15) { Value = docgia.LoaiDG.MaLoaiDG };
            SqlParameter sqlprHoTen = new SqlParameter("@HoTen", SqlDbType.NVarChar, 70) { Value = docgia.HoTen };
            SqlParameter sqlprNgaySinh = new SqlParameter("@NgaySinh", SqlDbType.Date) { Value = docgia.NgaySinh };
            SqlParameter sqlprDiaChi = new SqlParameter("@DiaChi", SqlDbType.NVarChar, 70) { Value = docgia.DiaChi };
            SqlParameter sqlprEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 70) { Value = docgia.Email };
            SqlParameter sqlprNgayLapThe = new SqlParameter("@NgayLapThe", SqlDbType.Date) { Value = docgia.NgayLapThe };
            SqlParameter sqlprNgayHetHan = new SqlParameter("@NgayHetHan", SqlDbType.Date) { Value = docgia.NgayHetHan };
            SqlParameter sqlprTongNo = new SqlParameter("@TongNo", SqlDbType.Money) { Value = docgia.TongNo };

            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaDocGia,
                sqlprMaLoaiDG, sqlprHoTen, sqlprNgaySinh, sqlprDiaChi, sqlprEmail, sqlprNgayLapThe, sqlprNgayHetHan, sqlprTongNo);
        }
    }
}
